const blogMessageBoardModel = require("../../model/blogMessageBoardModel")
const db = require("../../config/db");
const {QueryTypes} = require("sequelize");

class MessageBoardModelSer {
    // 发表留言
    async addMessage(addData) {
        const data = await blogMessageBoardModel.create(addData)
        return data
    }

    async getMessage(listQuery, searchData) {
        const data = await blogMessageBoardModel.findAndCountAll({
            where: {
                ...searchData
            },
            attributes: [
                ['id', 'id'],
                ['name', 'name'],
                ['number', 'number'],
                ['message', 'message'],
                ['updatedAt', 'updatedAt'],
                ['reply_time', 'replyTime'],
                ['reply_message_id', 'replyMessageId'],
            ],
            offset: (listQuery.page - 1) * listQuery.limit,
            limit: listQuery.limit,
            order: [['createdAt', 'DESC']]
        })
        return data
    }

    async getMessageTow(listQuery, searchData) {
        //需要查询的字段
        const searchFields = [
            'message_board.id AS id',
            'reply_time AS replyTime',
            'message',
            'reply_message_id AS replyMessageId',
            'blog_users.user_name AS name',
            'blog_users.user_photo_url AS userPhotoUrl',
            'message_board.updatedAt AS updatedAt'
        ]
        // sql语句前部分
        const SQL = `SELECT ${searchFields.join(',')} FROM message_board `
        // join部分
        const userJOIN = 'JOIN blog_users ON message_board.number = blog_users.user_number '
        // 分页部分
        const paging = `ORDER BY updatedAt DESC LIMIT ${listQuery.limit} OFFSET ${(listQuery.page - 1) * listQuery.limit}`
        // 查询条件部分
        let where = ''
        if (searchData.name) {
            where = ` WHERE blog_users.user_name = '${searchData.name} '`
        }
        if (searchData.reply_message_id) {
            if (!where) {
                where = `WHERE message_board.reply_message_id = '${searchData.reply_message_id}'`
            } else {
                where = where + ` AND message_board.reply_message_id = '${searchData.reply_message_id}'`
            }
        }
        const res = {}
        if (where) {
            res.data = (await db.query(SQL + userJOIN + where + paging, null))[0]
        } else {
            res.data = (await db.query(SQL + userJOIN + paging, null))[0]
        }

        const totalSql = 'SELECT COUNT(*) as total FROM message_board '
        const total = await db.query(totalSql + userJOIN + where)
        res.total = total[0][0].total
        return res
    }

    async deleteMessage(id) {
        const data = await blogMessageBoardModel.destroy(id)
        return data
    }

    //修改回复次数
    async editMessage(id) {
        const messageItem = await blogMessageBoardModel.findAll({
            where: {
                id
            },
            attributes: ['reply_time']
        })
        const number = Number(messageItem[0].dataValues.reply_time)
        await blogMessageBoardModel.update({reply_time: number + 1}, {where: {id}})
    }

    //根据日期范围查询留言
    async getMessageCount(start, end) {
        const sql = `SELECT id FROM message_board WHERE createdAt BETWEEN '${start}' AND '${end}'`
        const data = await db.query(sql, {type: QueryTypes.SELECT})
        return data
    }


}


module.exports = new MessageBoardModelSer()
